import pandas as pd
import numpy as np

# ******* 按照共有列的key合并 *****************
df = pd.DataFrame({
    "A": ["A0", "A1", "A2", "A3"],
    "B": ["B0", "B1", "B2", "B3"],
    "key": ["K0", "K1", "K2", "K3"]
})
df2 = pd.DataFrame({
    "C": ["C0", "C1", "C2", "C3"],
    "D": ["D0", "D1", "D2", "D3"],
    "key": ["K0", "K1", "K2", "K3"]
})


res = pd.merge(df, df2, on="key")
print(res)

# ******* 按照共有列的key合并，列值不一致 *****************
df = pd.DataFrame({
    "A": ["A0", "A1", "A2", "A3"],
    "B": ["B0", "B1", "B2", "B3"],
    "key": ["K0", "K1", "K2", "K3"],
    "key2": ["K0", "K1", "K0", "K1"]},
    index=["row0", "row1", "row2", "row3"])
df2 = pd.DataFrame({
    "C": ["C0", "C1", "C2", "C3"],
    "D": ["D0", "D1", "D2", "D3"],
    "key": ["K0", "K1", "K2", "K3"],
    "key2": ["K0", "K0", "K0", "K0"]},
    index=["row0", "row-1", "row2", "row-3"]
)
print(df)
print(df2)

# 默认是inner合并方法. how=["inner", "outer", "left", "right"]
res = pd.merge(df, df2, on=['key', 'key2'], how="inner")
print(res)


# left合并方法
res = pd.merge(df, df2, on=['key', 'key2'], how="left")
print(res)

# indicator会增加一列，表示合并的方式，[both, left_only, right_only]
res = pd.merge(df, df2, on="key2", how="outer", indicator=True)
print(res)


# ************ index merge ************
res = pd.merge(df, df2, left_index=True, right_index=True, how="outer")
print(res)
res = pd.merge(df, df2, left_index=True, right_index=True, how="inner")
print(res)

# ***************** 合并时，区分相同列名的不同数据 ********************
df = pd.DataFrame({
    "A": ["A0", "A1", "A2", "A3"],
    "B": ["B0", "B1", "B2", "B3"],
    "key": ["K0", "K1", "K2", "K3"]})
df2 = pd.DataFrame({
    "C": ["C0", "C1", "C2", "C3"],
    "B": ["D0", "D1", "D2", "D3"],
    "key": ["K0", "K-1", "K2", "K-2"]})

# 两个dataFrame的B相同，但是表示不同的意义的数据，需要在合并时保留两列
res = pd.merge(df, df2, on="key", suffixes=["_a", "_b"], how="inner")
print(res)
